package dbLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import dbLayer.DbConnection;
import modelLayer.PartOrder;
import modelLayer.Product;
import modelLayer.SalesOrder;

/**
 * 
 * @author Slayer
 *
 * There might be some few errors. Must check before launching.
 */
public class DBPartOrder implements IFDBPartOrder {

	private Connection con;
	
	public DBPartOrder()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public ArrayList<PartOrder> getAllPartOrder(boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return miscWhere("", retriveAssociation);
	}

	@Override
	public PartOrder findPartOrder(int id, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		String wClause = "  id = '" + id + "'";
        return singleWhere(wClause, retriveAssociation);
	}

	@Override
	/**
	 * JUNK JUNK JUNK !!!
	 */
	public PartOrder searchPartOrderOnName(String attValue, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchEmployye " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insert(PartOrder partOrder) throws Exception {
		// TODO Auto-generated method stub
		int nextID = GetMax.getMaxId("Select max(id) from partOrder");
		nextID = nextID + 1;
		
		System.out.println("Next ID : " + nextID);
		
		int rc = -1;
		
		String query = "INSERT INTO PartOrder(id, orderID, productID, amount)  VALUES('"+
		  		nextID + "','"  +
			    partOrder.getOrder().getID()  + "','"  +
	            partOrder.getProduct().getID() + "','" +
	            partOrder.getAmount() +"')";
		
		System.out.println("insert : " + query);
		
		try
		{
			Statement stmt = con.createStatement();
	        stmt.setQueryTimeout(5);
	        rc = stmt.executeUpdate(query);
	        stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("PartOrder ikke oprettet");
	        throw new Exception ("PartOrder is not inserted correct");
		}
		return rc;
	}

	@Override
	public int update(PartOrder part) {
		// TODO Auto-generated method stub
		PartOrder partOrder  = part;
		int rc=-1;

		String query="UPDATE PartOrder SET " +
		 	  "orderID ='"+ partOrder.getOrder().getID() +"', " +
		 	  "productID ='"+ partOrder.getProduct().getID() + "', " +
              "amount ='" + partOrder.getAmount()
		 	  + "'";
		
		System.out.println("Update query:" + query);
  		
		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in partOrder db: " + ex);
	  	}
		return rc;
	}

	@Override
	public int delete(int id) {
		// TODO Auto-generated method stub
		int rc=-1;
		  
	  	String query="DELETE FROM partOrder WHERE id = '" + id + "'";
	  	System.out.println(query);
	  	
	  	try
	  	{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	    catch(Exception ex)
   	    {
	 	  	System.out.println("Delete exception in partOrder db: "+ex);
	 	}
		return rc;
	}

	private ArrayList<PartOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<PartOrder> list = new ArrayList<PartOrder>();	
		
	    String query =  buildQuery(wClause);
  
        try{
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
        	while( results.next() ){
        		PartOrder partOrder = new PartOrder();
        		partOrder = buildPartOrder(results);	
        		list.add(partOrder);	
        	}
        	
            stmt.close();     
                
            if(retrieveAssociation)
            {
            	
            }
			
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
        
		return list;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT id, orderID, productID, amount  FROM PartOrder";
		
		if (wClause.length()>0)
			query = query + " WHERE " + wClause;
			
		return query;
	}
	
	private PartOrder buildPartOrder(ResultSet results)
    {   
		PartOrder partOrder = new PartOrder();
        try{
              partOrder.setID(results.getInt("id"));
              partOrder.setOrder(new SalesOrder(results.getInt("id")));
              partOrder.setProduct(new Product(results.getInt("id")));
              partOrder.setAmount(results.getInt("amount"));
        }
        catch(Exception e)
        {
           System.out.println("error in building the PartOrder object");
        }
        return partOrder;
    }
	
	private PartOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		PartOrder partOrder = new PartOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			partOrder = buildPartOrder(results);
                            
	 			stmt.close();
	 			if(retrieveAssociation)
	 			{
                               
	 			}
			}
	 		else{
	 			partOrder = null;
	 		}
		}
		catch (Exception e)
		{
	 		System.out.println("Query exception: "+ e);
	 	}
		return partOrder;
	}
}
